package com.web.utils;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class IQueryRunner extends QueryRunner {

	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

	static {
		/*
		try {
			dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
			dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false");
			dataSource.setUser("root");
			dataSource.setPassword("a123321000");

			dataSource.setInitialPoolSize(10);
			dataSource.setMinPoolSize(10);
			dataSource.setMaxPoolSize(40);
			dataSource.setCheckoutTimeout(30000);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}*/
	}

	public IQueryRunner() {
		super(dataSource);
	}

	/**
	 * getConnection
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}

	public void closeConnection(Connection connection) {
		try {
			if (connection == null || connection.isClosed())
				return;
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * getDataSource
	 * 
	 * @return
	 */
	public DataSource getDataSource() {
		return dataSource;
	}

	/**
	 * 处理多条件查询
	 */
	public ParamsAndSql getParamsAndSql(String tableName, Map<String, Object> data) {
		return getParamsAndSql(tableName, "*", null, null, data);
	}

	/**
	 * 处理多条件查询
	 */
	public ParamsAndSql getParamsAndSql(String tableName, String column, Map<String, Object> data) {
		return getParamsAndSql(tableName, column, null, null, data);
	}

	/**
	 * 处理多条件查询
	 */
	public ParamsAndSql getParamsAndSql(String tableName, Integer page, Integer pagesize, Map<String, Object> data) {
		return getParamsAndSql(tableName, "*", page, pagesize, data);
	}

	/**
	 * 获取总数
	 */
	public ParamsAndSql getCountParamsAndSql(String tableName, Map<String, Object> data) {
		return getParamsAndSql(tableName, "count(0)", null, null, data);
	}

	/**
	 * 处理多条件查询
	 */
	public ParamsAndSql getParamsAndSql(String tableName, String column, Integer page, Integer pagesize, Map<String, Object> data) {
		//page,pagesize为保留字段
		data.remove("page");
		data.remove("pagesize");
		String sql = "SELECT " + column + " FROM " + tableName + " WHERE 1=1";
		List<Object> params = new ArrayList<Object>();
		for (Map.Entry<String, Object> item : data.entrySet()) {
			String key = item.getKey();
			Object value = item.getValue();
			if (value != null) {
				sql += " AND " + key + " = ?";
				params.add(value);
			}
		}
		if (page != null && pagesize != null) {
			sql += " LIMIT " + (page * pagesize) + "," + pagesize;
		}
		System.out.println(sql);
		ParamsAndSql pas = new ParamsAndSql();
		pas.setParams(params.toArray());
		pas.setSql(sql);
		return pas;
	}

	/**
	 * 处理批量插入数据
	 */
	public ParamsAndSql getInsertParamsAndSql(String tableName, List<Map<String, Object>> list) {
		String sql = "INSERT INTO " + tableName + " (";
		Map<String, Object> item0 = list.get(0);

		for (Map.Entry<String, Object> item : item0.entrySet()) {
			sql += item.getKey() + ",";
		}
		sql = sql.substring(0, sql.length() - 1);
		sql += ") VALUES ";

		List<Object> params = new ArrayList<Object>();
		for (Map<String, Object> item : list) {
			sql += "(";
			for (Map.Entry<String, Object> map : item.entrySet()) {
				params.add(map.getValue());
				sql += "?,";
			}
			sql = sql.substring(0, sql.length() - 1);
			sql += "),";
		}
		sql = sql.substring(0, sql.length() - 1);

		System.out.println(sql);
		ParamsAndSql pas = new ParamsAndSql();
		pas.setParams(params.toArray());
		pas.setSql(sql);
		return pas;
	}

}
